package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import entity.Contents;
import util.DBHelper;

public class ContentsDAO {
	private Connection conn = null;
	private Statement st = null;

	public ContentsDAO() {
		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433;DatabaseName=Contract", "sa",
					"123456");
			st = conn.createStatement();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public synchronized void release() {
		if (st != null) {
			try {
				st.close();
			} catch (Exception e) {
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public synchronized List loadAllContents() throws Exception {
		List result = new ArrayList();
		try {
			ResultSet rs = st.executeQuery("select * from Contents where cstate='1'");
			while (rs.next()) {
				Contents contents = new Contents();
				contents.setCno(rs.getString("cno"));
				contents.setCname(rs.getString("cname"));
				contents.setCprice(rs.getString("cprice"));
				contents.setPaystate(rs.getString("paystate"));
				contents.setCstate(rs.getString("cstate"));
				contents.setMclause(rs.getString("mclause"));
				contents.setDrafter(rs.getString("drafter"));
				contents.setApprover(rs.getString("approver"));
				contents.setCstart(rs.getString("cstart"));
				contents.setCend(rs.getString("cend"));
				contents.setOther(rs.getString("other"));
				result.add(contents);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}
		return result;
	}

	public synchronized List loadAllContents2() throws Exception {
		List result = new ArrayList();
		try {
			ResultSet rs = st.executeQuery("select * from Contents where cstate='2'");
			while (rs.next()) {
				Contents contents = new Contents();
				contents.setCno(rs.getString("cno"));
				contents.setCname(rs.getString("cname"));
				contents.setCprice(rs.getString("cprice"));
				contents.setPaystate(rs.getString("paystate"));
				contents.setCstate(rs.getString("cstate"));
				contents.setMclause(rs.getString("mclause"));
				contents.setDrafter(rs.getString("drafter"));
				contents.setApprover(rs.getString("approver"));
				contents.setCstart(rs.getString("cstart"));
				contents.setCend(rs.getString("cend"));
				contents.setOther(rs.getString("other"));
				result.add(contents);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}
		return result;
	}
	public synchronized void addContents(Contents contents) throws Exception {
		try {
			ResultSet rs = st.executeQuery("select * from contents where cno='" + contents.getCno() + "'");
			if (rs.next()) {
				throw new Exception("合同号不能重复");
			} else {
				String sql = "insert into contents(cno,cname,cprice,paystate,cstate,mclause,drafter,approver,cstart,cend,other) values('"
						+ contents.getCno() + "','" + contents.getCname() + "','" + contents.getCprice() + "','"
						+ contents.getPaystate() + "','" + 1 + "','" + contents.getMclause() + "','"
						+ contents.getDrafter() + "','" + contents.getApprover() + "','" + contents.getCstart() + "','"
						+ contents.getCend() + "','" + contents.getOther() + "')";
				st.execute(sql);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}

	}

	public synchronized void saveContents(Contents contents) throws Exception {
		try {
			ResultSet rs = st.executeQuery("select * from contents where cno='" + contents.getCno() + "'");
			if (!rs.next()) {
				throw new Exception("合同号不存在");
			}
			String sql = "update contents(cno,cname,cprice,cstate,mclause,drafter,approver,cstart,cend,other) values('"
					+ contents.getCno() + "','" + contents.getCname() + "','" + contents.getCprice() + "','"
					+ contents.getCstate() + "','" + contents.getMclause() + "','" + contents.getDrafter() + "','"
					+ contents.getApprover() + "','" + contents.getCstart() + "','" + contents.getCend() + "','"
					+ contents.getOther() + "')";
			st.execute(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}

	}

	public synchronized void editContents(String cno) throws Exception {
		try {
			ResultSet rs = st.executeQuery("select * from contents where cno='" + cno + "'");
			if (!rs.next()) {
				throw new Exception("合同号不存在");
			}

			String sql1 = "update Contents set cstate='2' where cno='" + cno + "'";
			st.execute(sql1);
			String sql2 = "insert into execution(cno,context) values('" + cno + "','未执行')";
			st.execute(sql2);
			ResultSet rs1 = st.executeQuery("select * from contents where cno='" + cno + "'");
			if (rs1.next()) {
				String rs2 = rs1.getString("cprice");
				String rs3 = rs1.getString("paystate");
				if (rs3.equals("1")) {
					rs3 = "收";
				} else {
					rs3 = "付";
				}
				String sql3 = "insert into payment(cno,paystate,payprice,contractp) values('" + cno + "','" + rs3
						+ "','0','" + rs2 + "')";
				st.execute(sql3);
			}

		} catch (

		SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}

	}

	public synchronized void delContents(String cno) throws Exception {
		try {
			ResultSet rs = st.executeQuery("select * from contents where cno='" + cno + "'");
			if (!rs.next()) {
				throw new Exception("合同号不存在");
			}
			String sql = "delete from contents where cno='" + cno + "'";
			st.execute(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}
	}

	public synchronized Contents readContents(String cno) throws Exception {
		Contents result = null;
		try {
			ResultSet rs = st.executeQuery("select * from contents where cno='" + cno + "'");
			if (rs.next()) {
				result = new Contents();
				result.setCno(rs.getString("cno"));
				result.setCname(rs.getString("cname"));
				result.setCprice(rs.getString("cprice"));
				result.setCstate(rs.getString("cstate"));
				result.setMclause(rs.getString("mclause"));
				result.setDrafter(rs.getString("drafter"));
				result.setApprover(rs.getString("approver"));
				result.setCstart(rs.getString("cstart"));
				result.setCend(rs.getString("cend"));
				result.setOther(rs.getString("other"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}

		return result;

	}
	public synchronized List<Integer> acquire() throws Exception {
		List<Integer> data = new ArrayList<Integer>();
		try {
			ResultSet rsa = st.executeQuery("select count(*) from contents where cstate='1' ");
			if (rsa.next()) {
				data.add(rsa.getInt(1));
			}
			ResultSet rsb = st.executeQuery("select count(*) from contents where cstate='2'");
			if (rsb.next()) {
				data.add(rsb.getInt(1));
			}
			ResultSet rsc = st.executeQuery("select count(*) from execution");
			if (rsc.next()) {
				data.add(rsc.getInt(1));
			}
			ResultSet rsd = st.executeQuery("select count(*) from payment");
			if (rsd.next()) {
				data.add(rsd.getInt(1));
			}
			return data;
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}

	}

	public synchronized List<Integer> geta() throws Exception {
		List<Integer> data = new ArrayList<Integer>();
		try {
			ResultSet rsa = st.executeQuery("select count(*) from contents where cstate='2' and cend<'2017-06-24'");
			if (rsa.next()) {
				data.add(rsa.getInt(1));
			}
			ResultSet rsb = st.executeQuery(
					"select count(*) from contents,execution where contents.cno=execution.cno and cstate='2' and cend<'2017-06-19'and context='鎵ц涓�'");
			if (rsb.next()) {
				data.add(rsb.getInt(1));
			}
			return data;
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}

	}
}
